package classconverstation.cscl.ist.psu.edu.server.classcommons;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import classconverstation.cscl.ist.psu.edu.client.messengers.ClassCommonsMessage;
import classconverstation.cscl.ist.psu.edu.client.messengers.User;
import classconverstation.cscl.ist.psu.edu.client.services.ClassCommonsService;
import classconverstation.cscl.ist.psu.edu.server.DBConnector;
import classconverstation.cscl.ist.psu.edu.server.UserChecker;

import com.allen_sauer.gwt.log.client.Log;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

/*
 * @Honglu Du
 * This class deal with service side processes with ClassCommons
 */

public class ClassCommonsServiceImpl extends RemoteServiceServlet implements ClassCommonsService {


	public boolean postMessage(ClassCommonsMessage msg, int cid) {
		boolean posted = false;
		try {
			Connection conn = DBConnector.getInstance().getConnection();
			String insertMsg = "INSERT INTO classcommons (uid, content, cid, vLink, iLink, mtype) VALUES (?, ?, ?, ?, ?,?)";
			PreparedStatement ps = conn.prepareStatement(insertMsg);
			ps.setString(1, msg.getPoster().getUid());
			ps.setString(2, msg.getContent());
			ps.setInt(3, cid);
			ps.setString(4, msg.getVideoURL());
			ps.setString(5, msg.getImageURL());
			ps.setInt(6, msg.getType());
			ps.addBatch();
			ps.executeBatch();

			ps.close();
			conn.close();
			posted  = true;
		}
		catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
			System.out.println("Error when inserting a message to the database");
			sqle.printStackTrace();			
		}

		return posted;
	}


	public int getCid(String course_name) {
		int course_id = 0;
		try {
			Connection conn = DBConnector.getInstance().getConnection();
			String sql = "SELECT cid from class WHERE cname = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, course_name);

			ResultSet result = ps.executeQuery();
			if (result.next()) {
				course_id = result.getInt("cid");
			}
			conn.close();
			ps.close();

		}
		catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
			System.out.println("Error when locating course_id");
			sqle.printStackTrace();
		}
		return course_id;
	}


	public ArrayList<ClassCommonsMessage> loadClassCommonsMsgs(int course_id) {
		ArrayList<ClassCommonsMessage> msgs = new ArrayList<ClassCommonsMessage>();
		try {
			Connection conn = DBConnector.getInstance().getConnection();
			String sql = "SELECT * from classcommons WHERE cid = ? ORDER BY mid DESC";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, course_id);

			ResultSet result = ps.executeQuery();
			while (result.next()) {
				int mid = result.getInt("mid");
				int cid = result.getInt("cid");
				String uid = result.getString("uid");
				User user = UserChecker.getUser(uid);
				String content = result.getString("content");
				String vLink = result.getString("vLink");
				String iLink = result.getString("iLink");
				Date pDate = result.getTimestamp("time");
				int mType = result.getInt("mtype");
				boolean isTopic = false;
				if (mType != 0){
					isTopic = true;
				}
				int likes = result.getInt("likes");
				ClassCommonsMessage msg = new ClassCommonsMessage(mid, cid, user, content, vLink, iLink, pDate, isTopic, likes, mType);
				msgs.add(msg);
			}
			conn.close();
			ps.close();

		}
		catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
			System.out.println("Error when loading ClassCommons messages");
			sqle.printStackTrace();
		}
		return msgs;	
	}

	public ArrayList<ClassCommonsMessage> loadClassCommonsMsgs(int course_id, long time) {
		ArrayList<ClassCommonsMessage> msgs = new ArrayList<ClassCommonsMessage>();
		try {
			Connection conn = DBConnector.getInstance().getConnection();
			String sql = "SELECT * from classcommons WHERE (cid = ? and time > ?) ORDER BY mid DESC";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, course_id);
			ps.setTimestamp(2, new Timestamp(time));
			ResultSet result = ps.executeQuery();
			while (result.next()) {
				int mid = result.getInt("mid");
				int cid = result.getInt("cid");
				String uid = result.getString("uid");
				User user = UserChecker.getUser(uid);
				String content = result.getString("content");
				String vLink = result.getString("vLink");
				String iLink = result.getString("iLink");
				Date pDate = result.getTimestamp("time");
				int mType = result.getInt("mtype");
				boolean isTopic = false;
				if (mType != 0){
					isTopic = true;
				}
				int likes = result.getInt("likes");
				ClassCommonsMessage msg = new ClassCommonsMessage(mid, cid, user, content, vLink, iLink, pDate, isTopic, likes, mType);
				msgs.add(msg);
			}
			conn.close();
			ps.close();
		}
		catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
			System.out.println("Error when loading ClassCommons messages for the public display");
			sqle.printStackTrace();
		}
		return msgs;    
	}


	public int updateLikeCount(int mid) {

		int result = 0;
		try {
			Connection conn = DBConnector.getInstance().getConnection();

			String sql = "UPDATE classcommons SET likes = likes + 1 WHERE mid  = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, mid);

			ps.executeUpdate();
			ps.close();
			conn.close();
			result =1;
		}
		catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
			result = 0;
		}

		return result;
	}


	public boolean updatePinState(ClassCommonsMessage msg, int finalState) {
		boolean updated = false;
		try {
			Connection conn = DBConnector.getInstance().getConnection();

			String sql = "UPDATE classcommons SET mtype = ? WHERE mid  = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, finalState);
			ps.setInt(2, msg.getMid());
			ps.executeUpdate();
			ps.close();
			conn.close();
			updated = true;
		}
		catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}

		return updated;
	}


}
